package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.POSUserInfoDao;

import net.sf.json.JSONObject;
@Repository
public class POSUserInfoDaoImpl extends P2PJdbcDao implements POSUserInfoDao{

	@Override
	public List<Map<String,Object>> queryPOSUserInfoList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("START_DATE");
		String endDate = params.optString("END_DATE");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String posCode = params.optString("POS_CODE");
		String posOwnerSjhm = params.optString("POS_OWNER_SJHM");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		paramMap.put("posCode", posCode);
		paramMap.put("posOwnerSjhm", posOwnerSjhm);
		sql.append(" SELECT POS_CODE,POS_MONEY,POS_OWNER_SJHM,POS_BIZ_GYH,POS_OWNER_USER_ID, ");
		sql.append(" date_format(OPER_DATE,'%Y-%m-%d %H:%i:%s') as OPER_DATE ");
		sql.append(" FROM t_zhfw_pos_info where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND OPER_DATE >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND OPER_DATE <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(posCode)){
			sql.append(" AND POS_CODE like "+"'%"+posCode+"%' ");
		}
		if(StringUtils.isNotEmpty(posOwnerSjhm)){
			sql.append(" AND POS_OWNER_SJHM like "+"'%"+posOwnerSjhm+"%' ");
		}
		sql.append(" ORDER BY OPER_DATE DESC LIMIT :start,:limit ");
		System.out.println(sql.toString());
//		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<List<Map<String,Object>>>() {
			@Override
			public List<Map<String,Object>> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
				while(rs.next()){
					Map<String,Object> map = new HashMap<>();
					String posOwnerSjhm = rs.getString("POS_OWNER_SJHM");
					String posBizGyh = rs.getString("POS_BIZ_GYH");
					if((posOwnerSjhm==null)||(posOwnerSjhm.equals(""))){
						map.put("POS_OWNER_SJHM", null);
					}else if(posOwnerSjhm.length()>=4){
						String str = posOwnerSjhm.substring(0,3)+"****"+posOwnerSjhm.substring(posOwnerSjhm.length()-4,posOwnerSjhm.length());
						map.put("POS_OWNER_SJHM", str);
					}else{
						String str = posOwnerSjhm.substring(0)+"****";
						map.put("POS_OWNER_SJHM", str);
					}
					if((posBizGyh==null)||(posBizGyh.equals(""))){
						map.put("POS_BIZ_GYH", null);
					}else if(posBizGyh.length()>=4){
						String str = posBizGyh.substring(0,3)+"***"+posBizGyh.substring(posBizGyh.length()-3,posBizGyh.length());
						map.put("POS_BIZ_GYH", str);
					}else{
						String str = posBizGyh.substring(0)+"***";
						map.put("POS_BIZ_GYH", str);
					}
					map.put("OPER_DATE", rs.getString("OPER_DATE"));
					map.put("POS_CODE", rs.getString("POS_CODE"));
					map.put("POS_MONEY", rs.getString("POS_MONEY"));
					map.put("POS_OWNER_USER_ID", rs.getString("POS_OWNER_USER_ID"));
					list.add(map);
				}
				return list;
			}
		});
	}

	@Override
	public int getPOSUserInfoCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("START_DATE");
		String endDate = params.optString("END_DATE");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String posCode = params.optString("POS_CODE");
		String posOwnerSjhm = params.optString("POS_OWNER_SJHM");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		paramMap.put("posCode", posCode);
		paramMap.put("posOwnerSjhm", posOwnerSjhm);
		sql.append(" SELECT COUNT(*) ");
		sql.append(" FROM t_zhfw_pos_info where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND OPER_DATE >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND OPER_DATE <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(posCode)){
			sql.append(" AND POS_CODE like "+"'%"+posCode+"%' ");
		}
		if(StringUtils.isNotEmpty(posOwnerSjhm)){
			sql.append(" AND POS_OWNER_SJHM like "+"'%"+posOwnerSjhm+"%' ");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

}
